wrong timestamp using strftime('%s')

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

wrong timestamp using strftime('%s')

Dominik Ohnezeit
Hello,

 

I am trying to convert a date to timestamp, but after the conversion with
strftime('%s') the integer result is wrong

 

Example:

 

I insert a integer timestamp into a integer table column named CreationDate
with

strftime('%s', '1970-01-01 00:00:00.000')

 

After getting it from the table with

datetime(CreationDate, 'unixepoch')

or

datetime(CreationDate)

 

the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
22:29:11.000

 

 

Does anyone know why?

 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wrong timestamp using strftime('%s')

Richard Hipp-3
On 11/24/19, Dominik Ohnezeit <[hidden email]> wrote:
>
> strftime('%s', '1970-01-01 00:00:00.000')

Returns '0'.

>
> datetime(CreationDate, 'unixepoch')

Assuming CreationDate is 0, this returns '1970-01-01 00:00:00'.

>
> Does anyone know why?
>

Dunno why you might be getting anything different.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wrong timestamp using strftime('%s')

Jose Isaias Cabrera-4
In reply to this post by Dominik Ohnezeit

Dominik Ohnezeit, on Sunday, November 24, 2019 03:21 PM, wrote...

> strftime('%s', '1970-01-01 00:00:00.000')

[clip]

> the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
> 22:29:11.000
>
> Does anyone know why?

Here are some samples run...

sqlite> select strftime('%s', '1970-01-01 00:00:00.000');
0

sqlite> select strftime('%s', '1969-12-31 23:59:59');
-1

sqlite> select datetime(0, 'unixepoch');
1970-01-01 00:00:00

sqlite> select datetime(-1, 'unixepoch');
1969-12-31 23:59:59

You are probably subtracting 1 from 0 which is sending a -1 to datetime.  Just a thought...  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wrong timestamp using strftime('%s')

Keith Medcalf
In reply to this post by Dominik Ohnezeit




--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit <[hidden email]> wrote:

>I am trying to convert a date to timestamp, but after the conversion with
>strftime('%s') the integer result is wrong

>Example:

>I insert a integer timestamp into a integer table column named CreationDate with
>strftime('%s', '1970-01-01 00:00:00.000')

>After getting it from the table with

>datetime(CreationDate, 'unixepoch')

>or

>datetime(CreationDate)

>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31 22:29:11.000

This corresponds to Unixepoch time -5449

However, the builtin datetime function does not return milliseconds, only seconds, so it cannot return a text string ending in .000

>Does anyone know why?

Your wrapper is probably mucking about with the ISO timestring.

What is the value and type of CreationDate?



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wrong timestamp using strftime('%s')

Dominik Ohnezeit
the type of CreationDate is integer.
The CreationDate is inserted with strftime('%s', '1970-01-01 00:00:00.000')

need to check which value is written to the database.
I also tried without milliseconds - same result.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Keith Medcalf
Gesendet: Montag, 25. November 2019 22:41
An: SQLite mailing list
Betreff: Re: [sqlite] wrong timestamp using strftime('%s')





--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit <[hidden email]> wrote:

>I am trying to convert a date to timestamp, but after the conversion
>with
>strftime('%s') the integer result is wrong

>Example:

>I insert a integer timestamp into a integer table column named
>CreationDate with strftime('%s', '1970-01-01 00:00:00.000')

>After getting it from the table with

>datetime(CreationDate, 'unixepoch')

>or

>datetime(CreationDate)

>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
>22:29:11.000

This corresponds to Unixepoch time -5449

However, the builtin datetime function does not return milliseconds, only seconds, so it cannot return a text string ending in .000

>Does anyone know why?

Your wrapper is probably mucking about with the ISO timestring.

What is the value and type of CreationDate?



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: wrong timestamp using strftime('%s')

Keith Medcalf

The result of datetime(0, 'unixepoch') is '1970-01-01 00:00:00'.  This is what does not have milliseconds (the output).  It is a text string in the format YYYY-MM-DD HH:MM:SS.  Similarly datetime(0.123, 'unixepoch') is also '1970-01-01 00:00:00'.

The result of datetime(0) is, of course, '-471-11-24 12:00:00' (actually -4713-11-24 12:00:00) since this the proleptic Gregorian date corresponding to julian day number 0 where all date strings are UT1 AD and the year is limited to 4 positions, one of which is taken up by the - sign.

If looking at the result of the datetime() function execution shows milliseconds then something is interpreting the output of the datetime function before you see it, and it is likely that this processing that is what is causing the issue you are observing.

To get output of a unixepoch x with milliseconds in the string you would need to use the function strftime('%Y-%m-%d %H:%M:%f', x, 'unixepoch')

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users <[hidden email]> On
>Behalf Of Dominik Ohnezeit
>Sent: Monday, 25 November, 2019 14:49
>To: 'SQLite mailing list' <[hidden email]>
>Subject: Re: [sqlite] wrong timestamp using strftime('%s')
>
>the type of CreationDate is integer.
>The CreationDate is inserted with strftime('%s', '1970-01-01
>00:00:00.000')
>
>need to check which value is written to the database.
>I also tried without milliseconds - same result.
>
>
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:[hidden email]]
>Im Auftrag von Keith Medcalf
>Gesendet: Montag, 25. November 2019 22:41
>An: SQLite mailing list
>Betreff: Re: [sqlite] wrong timestamp using strftime('%s')
>
>
>
>
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit
><[hidden email]> wrote:
>
>>I am trying to convert a date to timestamp, but after the conversion
>>with
>>strftime('%s') the integer result is wrong
>
>>Example:
>
>>I insert a integer timestamp into a integer table column named
>>CreationDate with strftime('%s', '1970-01-01 00:00:00.000')
>
>>After getting it from the table with
>
>>datetime(CreationDate, 'unixepoch')
>
>>or
>
>>datetime(CreationDate)
>
>>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
>>22:29:11.000
>
>This corresponds to Unixepoch time -5449
>
>However, the builtin datetime function does not return milliseconds, only
>seconds, so it cannot return a text string ending in .000
>
>>Does anyone know why?
>
>Your wrapper is probably mucking about with the ISO timestring.
>
>What is the value and type of CreationDate?
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users