Applying offset to date/time creating UTC

[FlossyThePig]

I live in England and I am attempting to calculate payback time of my array of solar panels. Payment is paid for the export of any excess electricity generated by the array not used. I can obtain consumption and export data from my supplier in a consistent format.

I recently discovered that I can obtain actual data relating to electricity generated by my solar panels in the form of CSV files. Unfortunately the datetime values are in local time with an offset value because of British Summer Time (BST). I want to convert all the datetime values to UTC (i.e. Greenwich Mean Time).

I retired a couple of years ago after 40+ years working in the computer industry so programming is not a dark art for me. However, as a newcomer to LO basic I’m not sure where to begin.

Suppled date time data
2020-10-24 23:30:00 +0100
2020-10-25 00:00:00 +0100
2020-10-25 00:30:00 +0100
2020-10-25 01:00:00 +0100
2020-10-25 01:30:00 +0100
2020-10-25 01:00:00 +0000

Apply the offset to become
2020-10-24 22:30:00 +0000
2020-10-24 23:00:00 +0000
2020-10-24 23:30:00 +0000
2020-10-25 00:00:00 +0000
2020-10-25 00:30:00 +0000
2020-10-25 01:00:00 +0000

Unfortunately there are over 60,000 rows of data so a recursive routine is required.

A minor additional factor is that I am using an Apple Mac with MacOS Catalina.

I hope someone out there can point me in the right direction.

[Steph1]

Welcome @FlossyThePig!
Here is a possible approach:
AppliedDateTime.ods (16.2 KB)
The function DATEVALUE tries to evaluate a text as a date. A date in Calc is an integer number of days from 1899-12-31 (day 1).
The function TIMEVALUE tries to evaluate a text as a 24 hour time. A time in Calc is a real number between 0 and 1. So 1 hour is exactly 1/24.
In B2, I calculate DATEVALUE+TIMEVALUE and in case there is an hour to remove, I remove 1/24 with an IF function.
I needed to format the column as a datetime (YYYY-MM-DD HH:MM:SS) to see it as a readable date by a human, but Calc keeps it as a number.
In column C, I recreate a text as you want it although I don’t think that you can do much with that format, and certainly not produce a graph, use column B instead.
So apply the formula to the entire column. You can then either hide the initial column or copy / paste as value the column B over column A. (The former is probably better, so you can easily add new lines and drag down the formula).

[karolus]

as a first scetch:

from datetime import datetime as dt, UTC

stamps = """2020-10-24 23:30:00 +0100
2020-10-25 00:00:00 +0100
2020-10-25 00:30:00 +0100
2020-10-25 01:00:00 +0100
2020-10-25 01:30:00 +0100
2020-10-25 01:00:00 +0000""".splitlines()


for stamp in stamps:
    _date = dt.strptime(stamp,"%Y-%m-%d %H:%M:%S %z")
    print(f"{stamp} as utc is {_date.astimezone(UTC):%Y-%m-%d %H:%M:%S %z}")

that prints out:

2020-10-24 23:30:00 +0100 as utc is 2020-10-24 22:30:00 +0000

2020-10-25 00:00:00 +0100 as utc is 2020-10-24 23:00:00 +0000

2020-10-25 00:30:00 +0100 as utc is 2020-10-24 23:30:00 +0000

2020-10-25 01:00:00 +0100 as utc is 2020-10-25 00:00:00 +0000

2020-10-25 01:30:00 +0100 as utc is 2020-10-25 00:30:00 +0000

2020-10-25 01:00:00 +0000 as utc is 2020-10-25 01:00:00 +0000

For further help please post some lines of the original .csv which you want to convert

[Villeroy]

101143.ods (18.8 KB)

[karolus]

@Steph1 More generic (works with any offset):

=DATEVALUE(LEFT(A2;10))+TIMEVALUE(MID(A2;12;8))-MID(A2;21;3)/24
or straight:
=VALUE(LEFT(A2;19))-MID(A2;21;3)/24

[FlossyThePig]

Thank you karolus as the FOR routine should do what I want. However what should I put before the first line? Do I need some sort of reference to “com.sun.star…”?

[FlossyThePig]

First few lines in the CSV file:

Date/Time,Energy Produced (Wh)
2020-10-01 00:00:00 +0100,0
2020-10-01 00:15:00 +0100,0
2020-10-01 00:30:00 +0100,0
2020-10-01 00:45:00 +0100,0
2020-10-01 01:00:00 +0100,0
2020-10-01 01:15:00 +0100,0

When I tried your code I get a pop up
Basic Syntax error.
Expected: ,.

[FlossyThePig]

After a lot of head scratching, confused by code that was in Python and searching the internet I have produced a function which I hope will solve my problem. Thanks to Steph1 for the simple explanation af DATEVALUE and TIMEVALUE.

I now have more fun writing code to load data from about 40 CSV files

> Function ApplyOffset(sDateTime)
> 	' A simple function to apply offset value
> 	' to a DateTime string in ISO 6801 format
> 	Dim oDate
> 	Dim oTime
> 	Dim oOffset
> 	oDate = DATEVALUE(LEFT(sDateTime,10))
> 	oTime = TIMEVALUE(MID(sDateTime,12,8))
> 	oOffset = VAL(MID(sDateTime,21,3))
> 	ApplyOffset = odate + oTime - oOffset/24
> End Function
>  
>  
> Sub TestApplyOffset
> 	Dim sDateTime, sDate1, sDate2
> 	sDate1 = "2020-10-24 23:30:00 +0100"
> 	sDate2 = "2020-10-24 23:30:00 -0100"
> 	sDateTime = ApplyOffset(sDate1)
> 	MsgBox (sDate1 & " as utc is " & Format(sDateTime,"YYYY-MM-DD HH:MM"))
> 	sDateTime = ApplyOffset(sDate2)
> 	MsgBox (sDate2 & " as utc is " & Format(sDateTime,"YYYY-MM-DD HH:MM"))
> End Sub

P.S. How do I include code that looks like code?

[Steph1]

P.S. How do I include code that looks like code?

Copy your code in your reply, then select it and click the icon above

Function ApplyOffset(sDateTime)
’ A simple function to apply offset value
’ to a DateTime string in ISO 6801 format
Dim oDate
Dim oTime
Dim oOffset
oDate = DATEVALUE(LEFT(sDateTime,10))
oTime = TIMEVALUE(MID(sDateTime,12,8))
oOffset = VAL(MID(sDateTime,21,3))
ApplyOffset = odate + oTime - oOffset/24
End Function

Sub TestApplyOffset
Dim sDateTime, sDate1, sDate2
sDate1 = “2020-10-24 23:30:00 +0100”
sDate2 = “2020-10-24 23:30:00 -0100”
sDateTime = ApplyOffset(sDate1)
MsgBox (sDate1 & " as utc is " & Format(sDateTime,“YYYY-MM-DD HH:MM”))
sDateTime = ApplyOffset(sDate2)
MsgBox (sDate2 & " as utc is " & Format(sDateTime,“YYYY-MM-DD HH:MM”))
End Sub

[FlossyThePig]

[quote=“Steph1, post:9, topic:101143, full:true”]

P.S. How do I include code that looks like code?

Copy your code in your reply, then select it and click the icon above

It Works