Snowflake Date Time Context Functions

What are snowflake date and time context functions ?

All the date and time context functions in snowflake fall under general category.

Here is the list of date and time context functions:

  1. current_date()/current_date
  2. current_time()/current_time
  3. current_timestamp()/current_timestamp
  4. sysdate()

Date & Time Context Function Definition

Syntax Syntax (ANSI) Definition Output
(YYYY-MM-DD HH24:MI:SS.FF)
current_date() current_date Return the current date of the system 2021-07-24
current_time()
current_time(2)
current_time Return the current time of the system 17: 8:02 17:18:02.49
current_timestamp()
current_timestamp(2)
current_timestamp(4)
current_timestamp Return the current timestamp of the system 2021-07-24 17:18:02
2021-07-24 17:18:02.49
2021-07-24 17:18:02.4912
sysdate() NA Return the current timestamp of the system, but in the UTC time zone. 2021-07-24 17:18:02.4912
(YYYY-MM-DD HH24:MI:SS.FF4)

Important limitations to know sysdate()

  1. sysdate() context function requires parenthesis.
  2. sysdate() does not support a parameter to specify the precision of fractional seconds.
  3. Precision of fractional seconds for sysdate() is fixed (YYYY-MM-DD HH24:MI:SS.FF4)
  4. sysdate() returns current timestamp in UTC (Universal Time)
  5. sysdate() return value is TIMESTAMP_NTZ (No Time Zone = UTC), whereas current_timezone() returns TIMESTAMP_LTZ (Local Time Zone)
  6. Do not use the returned value of sysdate() for precise time ordering between concurrent queries (processed by the same virtual warehouse) because the queries might be serviced by different compute resources (in the warehouse).

Section 01 SQL Script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- lets see how the result appears without changing any time setting
select 
    current_date(),
    current_time(),
    current_timestamp(),
    sysdate()
    ;
-- these functions do need a running warehouse
-- they get it from metadata services

-- look at the date and time format

-- default fraction of second is with ff(3)

-- lets try these context functions without parenthesis (except sysdate all works)
select 
    current_date,
    current_time,
    current_timestamp,
    sysdate()
    ;

select sysdate;

-- ============================================================ --

-- LETS TRY WITH VARIALBE VALUES WITH AND WIHOUT SECOND FRACTION 

set (my_time, my_ts) = (current_time, current_timestamp);
select $my_time, $my_ts;

-- So if don't defint the second fraction parameters, it continue to display the FF(3)

-- WHAT IF WE SPECIFY FF(9), WILL IT CHANGE THE RESULT
set (my_time, my_ts) = (current_time(9), current_timestamp(9));
select $my_time, $my_ts;

/*
    NO CHANGE. SO WHY IT IS NOT CHANGING...???
    YOU HAVE TO USE THE SESSION LEVEL PARAMETERS
*/

alter session set timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
alter session set time_output_format = 'HH24:MI:SS.FF';
select current_time(9), current_timestamp(9);
set (my_time, my_ts,my_sysdate) = (current_time, current_timestamp,sysdate());
select $my_time, $my_ts, $my_sysdate;    

Section 02 SQL Script - How to use them to set column default value

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
create table demo01 (
  row_id integer autoincrement not null,
  dt_value date default current_date(),
  time_value time default current_time(),
  ts_value timestamp default current_timestamp(),
  sysdt_value timestamp default sysdate(),
  msg string
);

desc table demo01; 
-- interesting thing to notice about sysdt_value 
-- CAST(CONVERT_TIMEZONE('UTC', CAST(CURRENT_TIMESTAMP() AS TIMESTAMP_TZ(9))) AS TIMESTAMP_NTZ(9))

-- lets insert one row and see the result
insert into demo01 (msg) values ('1st row') ;
select * from demo01;

--since in this session, we have not set the date and time format, we can still see FF(3)
--now change the date and time format with FF
alter session set timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
alter session set time_output_format = 'HH24:MI:SS.FF';

select * from demo01;

-- why only FF(3)
-- let me insert 10 rows at one go..
insert into demo01 (msg) values 
    ('2nd row'),('3rd row'),('4th row'),('5th row'),('6th row'),('7th row'),('8th row'),('9th row'),('10th row') ;
select * from demo01;
-- all values are same, means that meta data layer get the value and then insert happens.

insert into demo01 (msg) values ('11th row');
insert into demo01 (msg) values ('12th row');
insert into demo01 (msg) values ('13th row');
insert into demo01 (msg) values ('14th row');
insert into demo01 (msg) values ('15th row');

select * from demo01;

Section 03 SQL Script - Timezone and Date/Time context functions

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- By default your current session is in your local timezone
select current_date,current_timestamp,sysdate();

-- you can change the session level parameters 
alter session set timezone = 'Japan';
alter session set timestamp_output_format = 'YYYY-MM-DD HH24:MI:SS.FF';
alter session set time_output_format = 'HH24:MI:SS.FF';

-- after setting my time zone as Japan, lets see the result
select current_date,current_timestamp, sysdate();

-- you noticed that sysdate is not changed with session value, it still shows the sysdate with UTC.

-- lets insert some record to our table

-- setting my context
use role sysadmin;
use database sales;
use schema sandbox;
select current_database() || '.' ||current_schema() || '(' || current_role() || ')' as context; -- lets check the context

insert into demo01 (msg) values ('21st row - Japan');
insert into demo01 (msg) values ('22nd row - Japan');
insert into demo01 (msg) values ('23rd row - Japan');
insert into demo01 (msg) values ('24th row - Japan');
insert into demo01 (msg) values ('25th row - Japan');

select * from demo01 where msg like '%Japan%';